USE SAS
GO

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetChildAccounts')
	BEGIN
		DROP  Procedure  dbo.GetChildAccounts
	END

GO

CREATE Procedure dbo.GetChildAccounts
	@isActive	VARCHAR(10) = NULL,
	@userId		VARCHAR(10)
AS
BEGIN

	IF(@isActive IS NOT NULL)
		SET @ISACTIVE = CASE WHEN @ISACTIVE IN ('X','1') THEN '1' ELSE '0' END

	;WITH A AS (
		-- ACCOUNT INFO
		SELECT
			p.PartnerNo			AS [AccountNo]
		,	p.PartnerName		AS [AccountName]
		,	p.ISACTIVE			AS [Active]
		,	p.PartnerGUID		as [PKID]
		FROM dbo.TR_PARTNER as p WITH (NOLOCK)
		INNER JOIN dbo.TR_RELATIONSHIPS as r WITH (NOLOCK)
			on r.SourceGUID = p.PartnerGUID
		WHERE
			p.PartnerCategory = 2 -- ORGANIZATION
		AND p.PartnerType = 2 -- CHILD ACCOUNT
		AND	r.RelationshipType = 3 -- OWNER
		AND r.Partner2 = @userID
		AND ISActive = ISNULL(@isActive, ISActive)
	), B AS (
		-- PARENT ACCOUNT
		SELECT
			p.PartnerName		AS [ParentAccountName]
		,	A.PKID
		FROM TR_PARTNER as p WITH (NOLOCK)
		INNER JOIN TR_RELATIONSHIPS as r WITH (NOLOCK)
			ON r.Partner2 = p.PartnerNo
		INNER JOIN A 
			ON A.PKID = r.SourceGUID
		WHERE
			r.RelationshipType = 7 -- PARENT ACCOUNT
	), C AS (
		-- ADDRESS
		SELECT
			ad.Street1 + ', ' + ad.Street2	as [Street]
		,	c.CityName		AS [City]
		,	ad.State
		,	ad.Zip
		,	ad.AddressGUID
		,	A.PKID
		FROM TR_ADDRESS as ad WITH (NOLOCK)
		INNER JOIN A 
			on A.PKID = ad.PartnerGUID
		INNER JOIN TR_CITY as c WITH (NOLOCK)
			ON ad.CityNo = c.CityNo
	), D AS (
		-- COMMUNICATION
		SELECT
			com.Phone 
		,	C.PKID
		FROM TR_COMMUNICATION as com WITH (NOLOCK)
		INNER JOIN C 
			on C.AddressGUID = com.AddressGUID
	), FINAL AS (
		SELECT
			A.AccountNo				AS [Company No]
		,	A.AccountName			AS [Company Name]
		,	B.ParentAccountName		AS [Parent Company]
		,	C.Street				AS [Street]
		,	C.City
		,	C.State
		,	D.Phone
		,	(CASE ISNULL(A.Active,0) WHEN 1 THEN 'Yes' ELSE 'No' END)	AS [Active]
		,	A.PKID
		FROM A
		LEFT OUTER JOIN B
			ON B.PKID = A.PKID
		LEFT OUTER JOIN C
			ON C.PKID = A.PKID
		LEFT OUTER JOIN D
			ON D.PKID = A.PKID
	)


	SELECT * FROM FINAL
	ORDER BY [Active] DESC, [Company Name] ASC

END
GO


-- GetChildAccounts @userID = 3